import json

from flask import Blueprint, request
from werkzeug.datastructures import FileStorage

from apps.db import db
from apps.logger_config import logger
from apps.model import File
from apps.service import delete_files_by_cid, delete_all_data
from apps.utils import Response, getRequestData, model_dict_clear, obj_underline2hump
from apps.utils.static_file import delete_file

history = Blueprint('history', __name__)


def get_date(params: dict, key: str, default_value: str):
    value = params.get(key, default_value)
    if value is None:
        return default_value
    return value


@history.route('/list', methods=['POST', 'GET'])
def get_history_list():
    params = getRequestData()
    state = params.get('state')
    page_size = params.get('pageSize', 10)
    page = params.get('current', 1)
    if state == 'waitCompleted' or state == 'confirmed':
        # 待完成
        yes_or_no = ''
        if state == 'confirmed':
            yes_or_no = 'not'
        total_sql = """
                select count(1) as amount from tb_bank_side_info b
                where b.custom_name like '%{userName}%'
                  and b.create_time between '{startDate}' and '{endDate}'
                  and b.state = 'confirmed'
                  and b.id {yes_or_no} in (select contract_id from tb_bank_side_extend where id in (select extend_id from tb_house_confirm))
            """.format(
            userName=get_date(params, 'userName', ''),
            startDate=get_date(params, 'startDate', '1900-01-01'),
            endDate=get_date(params, 'endDate', '2999-01-01'),
            yes_or_no=yes_or_no
        )

        sql = """
            select b.*
            from tb_bank_side_info b
            where b.custom_name like '%{userName}%'
              and b.create_time between '{startDate}' and '{endDate}'
              and b.state = 'confirmed'
              and b.id {yes_or_no} in (select contract_id from tb_bank_side_extend where id in (select extend_id from tb_house_confirm))
            order by b.create_time desc limit {size} offset {offset}
            """.format(
            userName=get_date(params, 'userName', ''),
            startDate=get_date(params, 'startDate', '1900-01-01'),
            endDate=get_date(params, 'endDate', '2999-01-01'),
            size=page_size,
            yes_or_no=yes_or_no,
            offset=(page - 1) * page_size
        )

    else:
        if state is None:
            stateCol = ''
        else:
            stateCol = "and b.state = '{state}'".format(state=state)
        total_sql = """
            select count(1) as amount from tb_bank_side_info b
            where b.custom_name like '%{userName}%'
              and b.create_time between '{startDate}' and '{endDate}'
              {stateCal}
        """.format(
            userName=get_date(params, 'userName', ''),
            startDate=get_date(params, 'startDate', '1900-01-01'),
            endDate=get_date(params, 'endDate', '2999-01-01'),
            stateCal=stateCol
        )

        sql = """
            select b.*
            from tb_bank_side_info b
            where b.custom_name like '%{userName}%'
              and b.create_time between '{startDate}' and '{endDate}'
              {stateCal}
            order by b.create_time desc limit {size} offset {offset}
            """.format(
            userName=get_date(params, 'userName', ''),
            startDate=get_date(params, 'startDate', '1900-01-01'),
            endDate=get_date(params, 'endDate', '2999-01-01'),
            stateCal=stateCol,
            size=page_size,
            offset=(page - 1) * page_size
        )
    total = db.session.execute(total_sql).first()[0]
    item_list = obj_underline2hump(db.session.execute(sql).fetchall())

    # 查询
    contract_ids = db.session.execute(
        'select contract_id from tb_bank_side_extend e where e.id in (select extend_id from tb_house_confirm)'
    ).fetchall()
    contract_ids = [i[0] for i in contract_ids]
    for item in item_list:
        if item.get('state') == 'approved':
            item['checked'] = True
        elif item.get('state') == 'confirmed':
            if item.get('id') in contract_ids:
                item['checked'] = True
        else:
            item['checked'] = False

    # logger.info("查询历史记录： sql: %s, 数据长度： %s", sql, len(item_list))
    return Response.success({'data': item_list, 'current': page, 'pageSize': page_size, 'total': total})


@history.route('/upload', methods=['POST'])
def upload_history_file():
    f: FileStorage = request.files['file']
    hid = dict(request.args).get('id')
    file = File.save_file_bytes(f, hid)
    logger.info('%s 文件上传至文件夹 %s', f.filename, file)
    db.session.commit()
    return Response.success({'id': file.id})


@history.route('/get_files', methods=['GET'])
def get_files():
    hid = dict(request.args).get('id')
    files = File.filter_by(history_id=hid).all()
    files = [model_dict_clear(d.__dict__) for d in files]
    return Response.success(files)


@history.route('/delete_file', methods=['DELETE'])
def delete_history_file():
    fid = dict(request.args).get('id')
    if fid is None:
        return Response.success()
    # 删除记录
    file = File.get(fid)
    file.delete()
    # 删除文件
    delete_file(fid)
    db.session.commit()
    return Response.success()


@history.route('/delete_files', methods=['DELETE'])
def delete_files():
    hid = dict(request.args).get('id')
    if hid is None:
        return Response.success()
    try:
        delete_files_by_cid(hid)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        return Response.error(str(e))
    return Response.success()


@history.route('/delete', methods=['DELETE'])
def delete_history():
    hid = dict(request.args).get('id')
    delete_all_data(hid)
    db.session.commit()
    return Response.success()


@history.route("/statistic", methods=['POST'])
def statistic():
    params = json.loads(request.data.decode('utf-8'))
    sql = """
    select state, count(1) as amount
    from tb_bank_side_info b
    where custom_name like '%{userName}%'
      and create_time between '{startDate}' and '{endDate}'
      and state != 'confirmed'
    group by state;
    """.format(userName=get_date(params, 'userName', ''), startDate=get_date(params, 'startDate', '1900-01-01'),
               endDate=get_date(params, 'endDate', '2999-01-01'))
    # logger.info('统计数据： sql: %s', sql)
    data = db.session.execute(sql).fetchall()

    # 已经找到正式权证的一手房数据
    sql = """
        select 'waitCompleted' as state, count(1) as amount
        from tb_bank_side_info b
        where custom_name like '%{userName}%'
            and create_time between '{startDate}' and '{endDate}'
            and state = 'confirmed'
            AND b.id IN ( SELECT contract_id FROM tb_bank_side_extend WHERE id IN ( SELECT extend_id FROM tb_house_confirm ) )
    """.format(
        userName=get_date(params, 'userName', ''),
        startDate=get_date(params, 'startDate', '1900-01-01'),
        endDate=get_date(params, 'endDate', '2999-01-01')
    )
    # logger.info('统计数据： sql: %s', sql)
    data1 = db.session.execute(sql).fetchall()
    data += data1

    # 还没有找到正式权证的一手房数据
    sql = """
            select 'confirmed' as state, count(1) as amount
            from tb_bank_side_info b
            where custom_name like '%{userName}%'
                and create_time between '{startDate}' and '{endDate}'
                and state = 'confirmed'
	            AND b.id NOT IN ( SELECT contract_id FROM tb_bank_side_extend WHERE id IN ( SELECT extend_id FROM tb_house_confirm ) )
        """.format(
        userName=get_date(params, 'userName', ''),
        startDate=get_date(params, 'startDate', '1900-01-01'),
        endDate=get_date(params, 'endDate', '2999-01-01')
    )
    # logger.info('统计数据： sql: %s', sql)
    data1 = db.session.execute(sql).fetchall()
    data += data1
    return Response.success(data)
